#!/usr/bin/env python
# coding: utf-8

# # 读取数据

# In[74]:


import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
data=pd.read_excel("C:/Users/望岳/Desktop/毕业设计/2021烘粮记录.xlsx")
print(data)


# # 数据清洗

# In[107]:


def dataclean(ele):#利用3σ法进行数据清洗
    a = data[ele].mean()
    b = data[ele].std()
    c=a-3*b, 
    d=a+3*b
    df=data.loc[(data[ele] > d) | (data[ele] < c)]
    print(a,b,c,d)
    print(df.index)#查找异常值所在的行
def datachoice():#对每一列进行异常值查找
    for c in ["炉温","炉排","风温1","风温2","风温3","排粮量","干粮水分","原粮水分"]:
        dataclean(c)
datachoice()#调用函数


# In[108]:


#删除异常值
data=data.drop(labels=[0,5,6,13,14,15,17,30,34,38,448,500,44,45,94,176,473,24,44,45,94,473,532,94,220,258,368,467,473,333,334,186,187,536,595,484,485,486,487,488,489,490,491])
print(data)


# # 绘制变量之间的散点图

# In[111]:


for x in ["炉温","炉排","风温1","风温2","风温3","排粮量","干粮水分","原粮水分"]:
    for y in ["炉温","炉排","风温1","风温2","风温3","排粮量","干粮水分","原粮水分"]:
        sns.jointplot(x=data[x], y=data[y], #设置xy轴，显示columns名称
              data = data,  #设置数据
              color = 'b', #设置颜色
              #s = 50, edgecolor = 'w', linewidth = 1,#设置散点大小、边缘颜色及宽度(只针对scatter)
              kind = 'reg',#设置类型：'scatter','reg','resid','kde','hex'
              #stat_func=<function pearsonr>,
              space = 0.1, #设置散点图和布局图的间距
              height = 8, #图表大小(自动调整为正方形))
              ratio = 3, #散点图与布局图高度比，整型
              # marginal_kws = dict(bins=15, rug =True), #设置柱状图箱数，是否设置rug
              )
        


# In[123]:


#设置风格
sns.set_style('white',{'font.sans-serif':['simhei','Arial']})
sns.pairplot(data, 
             kind = 'scatter', #散点图/回归分布图{'scatter', 'reg'})
             diag_kind = 'hist', #直方图/密度图{'hist'， 'kde'} 
             #hue = 'species',   #按照某一字段进行分类      
             palette = 'husl',  #设置调色板      
             markers = ['o', 's', 'D'], #设置不同系列的点样式（这里根据参考分类个数）   
             height = 2  #图标大小            
            )


# # 2020年数据处理

# In[112]:


data1=pd.read_excel("C:/Users/望岳/Desktop/毕业设计/2020烘粮记录.xlsx")
print(data1)


# In[115]:


def dataclean(ele):#利用3σ法进行数据清洗
    a = data1[ele].mean()
    b = data1[ele].std()
    c=a-3*b, 
    d=a+3*b
    df=data1.loc[(data1[ele] > d) | (data1[ele] < c)]
    print(a,b,c,d)
    print(df.index)#查找异常值所在的行
def datachoice():#对每一列进行异常值查找
    for c in ["炉温","炉排","风温1","风温2","风温3","排粮量","干粮水分%","原粮水分"]:
        dataclean(c)
datachoice()#调用函数


# In[120]:


#删除异常值
data1=data1.drop(labels=[271,410,540,31,688,738,386,387,388,389,8,27,72,78,79,373,31,46,47,50,67,72,78,79,80,373,629,527,672,673,674,675,676,677,679])
print(data1)


# In[122]:


for x in ["炉温","炉排","风温1","风温2","风温3","排粮量","干粮水分%","原粮水分"]:
    for y in ["炉温","炉排","风温1","风温2","风温3","排粮量","干粮水分%","原粮水分"]:
        sns.jointplot(x=data1[x], y=data1[y], #设置xy轴，显示columns名称
              data = data1,  #设置数据
              color = 'b', #设置颜色
              #s = 50, edgecolor = 'w', linewidth = 1,#设置散点大小、边缘颜色及宽度(只针对scatter)
              kind = 'reg',#设置类型：'scatter','reg','resid','kde','hex'
              #stat_func=<function pearsonr>,
              space = 0.1, #设置散点图和布局图的间距
              height = 8, #图表大小(自动调整为正方形))
              ratio = 3, #散点图与布局图高度比，整型
              # marginal_kws = dict(bins=15, rug =True), #设置柱状图箱数，是否设置rug
              )


# In[124]:


#设置风格
sns.set_style('white',{'font.sans-serif':['simhei','Arial']})
sns.pairplot(data1, 
             kind = 'scatter', #散点图/回归分布图{'scatter', 'reg'})
             diag_kind = 'hist', #直方图/密度图{'hist'， 'kde'} 
             #hue = 'species',   #按照某一字段进行分类      
             palette = 'husl',  #设置调色板      
             markers = ['o', 's', 'D'], #设置不同系列的点样式（这里根据参考分类个数）   
             height = 2  #图标大小            
            )


# In[ ]:




